﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace VietKinsNutrition.Data
{
    public class ClsTblBaoanDAL
    {
        private int iValue;
        public ClsTblBaoanDAL(int _iValue)
        {
            iValue = _iValue;
        }
        public ClsTblBaoanDAL()
        { }
        ClsBindingBLL ClsBin = new ClsBindingBLL();

        public void Insert(int LopId, DateTime Ngay, int Tongsosuat, int Tongsotre, int ThanghocId)
        {
            try
            {
                string NamePrc = QueryString.SPROC_TBLBAOAN_ADD.Replace("@Ngay", "#" + Ngay.ToString("MM/dd/yyyy") + "#").Replace("@Tongsosuat", Tongsosuat.ToString()).Replace("@Tongsotre", Tongsotre.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@LophocId", LopId.ToString());
                OleDbCommand Cmm = new OleDbCommand();
                Cmm.Connection = ClsConnection.OleDBConnect();
                Cmm.CommandText = NamePrc;
                ClsConnection.OleDbDAdpter(NamePrc, Cmm);
            } 
            catch
            {
            
            }
        }

        public DataSet BaoanbyNgay(DateTime Ngay)
        {
            DataSet Dsettong = new DataSet();

            DataSet ReturnSet = new DataSet();
            //QueryString.SPROC_TBLBAOAN_GETBYID = "SELECT * From TblBaoan WHERE Ngay = #" + Ngay.ToString("MM/dd/yyyy") + "# AND LophocId = " + lopId;
            QueryString.SPROC_TBLBAOAN_GETBYID = "SELECT TblBaoanKhoilop.Id, TblDm_Khoilop.Tenkhoilop, TblDm_Khoilop.Id AS KhoilopId, iif(isnull( TblBaoanKhoilop.Tongsosuat),0,TblBaoanKhoilop.Tongsosuat) AS tongsosuat, iif(isnull(  TblBaoanKhoilop.Tongsotre ),0,TblBaoanKhoilop.Tongsotre) AS Tongsotre,  iif(isnull(TblBaoanKhoilop.Ngay),#" + Ngay.ToString("MM/dd/yyyy") + "#,TblBaoanKhoilop.Ngay) As Ngay" +
           " FROM TblDm_Khoilop LEFT JOIN [SELECT TblBaoan.Id, TblBaoan.Ngay, TblBaoan.LophocId, TblBaoan.Tongsosuat, TblBaoan.Tongsotre, TblBaoan.ThanghocId " +
           " FROM TblBaoan WHERE ((TblBaoan.Ngay)=#" + Ngay.ToString("MM/dd/yyyy") + "#)]. AS TblBaoanKhoilop ON TblDm_Khoilop.Id = TblBaoanKhoilop.LophocId ";
            string NamePrc = QueryString.SPROC_TBLBAOAN_GETBYID;
            string NameTable = "TblBaoan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbDataAdapter Dap;

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            Dap = new OleDbDataAdapter(Cmm);
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap.Fill(Dset.Tables[NameTable]);
            return Dset;
        }

        public void Edit(int Id, int LopId, DateTime Ngay, int Tongsosuat, int Tongsotre, int ThanghocId)
        {
            string NamePrc = QueryString.SPROC_TBLBAOAN_UPDATE.Replace("@Id", Id.ToString()).Replace("@Ngay", "#" + Ngay.ToShortDateString() + "#").Replace("@Tongsosuat", Tongsosuat.ToString()).Replace("@Tongsotre", Tongsotre.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@LophocId", LopId.ToString());
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
    }
}
